package com.gec.dao.impl;

import com.gec.dao.DeptDao;
import com.gec.domain.Dept;
import com.gec.utils.JDBCUtils;
import com.gec.utils.PageModel;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DeptDaoImpl implements DeptDao {
    //添加部门
    @Override
    public void addDept(Dept dept) {
        String sql = "INSERT INTO dept_inf(NAME,REMARK)VALUE(?,?)";
        Object[] params = {dept.getName(), dept.getRemark()};
        try {
            JDBCUtils.executeUpdate(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(JDBCUtils.getConn(), JDBCUtils.getPs(), null);
        }
    }

    //查询部门
    @Override
    public List<Dept> findAllDept(Dept dept, PageModel model) {
        List<Dept> depts = new ArrayList<>();

        //动态查询
        StringBuffer sql = new StringBuffer("select * from dept_inf where 1=1 ");
        List<Object> objects = new ArrayList<>();

        ResultSet rs = null;
        try {
//            if (dept != null && !"".equals(dept.getName()) && dept.getName() != null) {
            //防止空指针异常
            if (dept != null && !"".equals(dept.getName()) && dept.getName() != null) {
                sql.append(" and name like ?");
                objects.add("%" + dept.getName() + "%");
            }
            //分页查询
            sql.append(" limit ").append(model.getStartRow()).append(",").append(PageModel.pageSize);

            //形成数组
            Object[] params = objects.toArray(new Object[objects.size()]);

            rs = JDBCUtils.executeQuery(sql.toString(), params);

            while (rs.next()) {
                Dept dept1 = new Dept();
                copy(rs, dept1);
                depts.add(dept1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(JDBCUtils.getConn(), JDBCUtils.getPs(), null);
        }
        return depts;
    }

    //删除部门
    @Override
    public void deleteDept(int id) {
        String sql = "DELETE FROM dept_inf WHERE id=?";
        Object[] params = {id};
        try {
            JDBCUtils.executeUpdate(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(JDBCUtils.getConn(), JDBCUtils.getPs(), null);
        }
    }

    //修改部门
    @Override
    public void updateDept(Dept dept) {
        String sql = "UPDATE dept_inf SET NAME=?,remark=? WHERE id=?";
        Object[] params = {dept.getName(), dept.getRemark(), dept.getId()};
        try {
            JDBCUtils.executeUpdate(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(JDBCUtils.getConn(), JDBCUtils.getPs(), null);
        }
    }


    @Override
    public int getTotalJobCount(Dept dept) {
        //获取总记录数
        StringBuffer sql = new StringBuffer("SELECT COUNT(0) FROM dept_inf WHERE 1=1  ");
        //参数：设置问号的值
        List<Object> objects = new ArrayList<>();
        ResultSet rs = null;
        try {
            if (dept != null && !"".equals(dept.getName()) && dept.getName() != null) {
                sql.append("  and name like ?");
                objects.add("%" + dept.getName() + "%");
            }
            //分页查询
            //动态参数数组 转换成数组
            Object[] params = objects.toArray(new Object[objects.size()]);
            rs = JDBCUtils.executeQuery(sql.toString(), params);

            while (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(JDBCUtils.getConn(), JDBCUtils.getPs(), rs);
        }
        return 0;
    }

    @Override
    public Dept findDeptById(int id) {
        String sql = "SELECT * FROM dept_inf WHERE id=?";
        Object[] params = {id};
        ResultSet rs = null;
        try {
            rs = JDBCUtils.executeQuery(sql, params);
            while (rs.next()) {
                Dept dept1 = new Dept();
                copy(rs, dept1);
                return dept1;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(JDBCUtils.getConn(), JDBCUtils.getPs(), null);
        }
        return null;
    }

    @Override
    public List<Dept> findDept(Dept dept) {
        List<Dept>depts=new ArrayList<>();
        String sql="select * from dept_inf";
        //Object[]params={dept};
        ResultSet rs=null;
        try{
            rs=JDBCUtils.executeQuery(sql);
            while(rs.next()){
                Dept dept1=new Dept();
                copy(rs,dept1);
                depts.add(dept1);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(JDBCUtils.getConn(),JDBCUtils.getPs(),rs);
        }
        return depts;
    }


    private void copy(ResultSet rs, Dept dept1) {
        try {
            dept1.setId(rs.getInt("id"));
            dept1.setName(rs.getString("name"));
            dept1.setRemark(rs.getString("remark"));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
